package com.list.fans.util;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Properties;


/**
 * 鐠愮喕鐭楁潻鐐村复閺佺増宓佹惔锟� 
 * 婵″倷缍嶇亸浣筋棅 閿涚噦绱� 闁絼绨洪弰顖氬綁閻拷 閿涳拷  SQL閺勵垰褰夐崠锟�   閸欏倹鏆熼弰顖氬綁閸栵拷     閺屻儴顕楅崪锟� DML閺勵垯绗夐崥宀�娈�   闁絼绨洪弰顖欑瑝閸欐娈� 閿涳拷     妞瑰崬濮╅弰顖欑瑝閸欐ê瀵�  鏉╃偞甯撮弰顖欑瑝閸欐娈�  閸忔娊妫� 娑旂喐妲告稉宥呭綁閻拷
 * @author SZT
 *
 */
public class SqlUtil {
	
	private  static  final  String  DRIVER="driver";
	private  static  final  String  URL ="url";
	private  static  final  String  USERNAME="username";
	private  static  final  String  PASSWORD="password";
	static  Properties proper =new Properties();
	//閺堫剙婀撮崣姗�鍣�  閹存牞锟藉懐鍤庣粙瀣綁闁诧拷
	static  ThreadLocal<Connection>   container=new  ThreadLocal<Connection>();
	
	//妫ｆ牕鍘�
	static{
		
//		try {
//			//闁俺绻冪猾璇插鏉炶棄娅� 閸旂姾娴囬崷銊ц鐠虹喕鐭惧鍕瑓閻ㄥ嫬鐫橀幀褔鍘ょ純顔芥瀮娴狅拷
//			proper.load(SqlUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
//		} catch (IOException e) {
//			e.printStackTrace();
//		}
	}
	
	
	//1閵嗕礁濮炴潪浠嬧攳閸旓拷
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	//2閵嗕浇骞忛崣鏍箾閹猴拷
	public static Connection   getConnection(){
		Connection  conn =container.get();
		try {
			if(conn==null  || conn.isClosed()){
				conn =	DriverManager.getConnection("jdbc:mysql://localhost:3306/project?useUnicode=true&characterEncoding=utf8&autoReconnect=true&rewriteBatchedStatements=TRUE","root","admin");
				container.set(conn);
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return  conn;
	}
	//鏌ヨ
	public  static ArrayList<HashMap<String,Object>>    select(String sql,Object ... params){
		ArrayList<HashMap<String,Object>>  list=new  ArrayList<HashMap<String,Object>>();
		Connection conn =	getConnection();
		PreparedStatement pst=null;
		ResultSet  rs=null;
		try {
			pst  =	conn.prepareStatement(sql);
			System.out.println(sql);
			if(params!=null){
				for(int i =0;i<params.length;i++){
					pst.setObject(i+1, params[i]);
				}
			}
			rs =  pst.executeQuery();
			//鐟欙絾鐎絉esultSet 鐟欙絾鐎介幋鎬榓shMap 閸滃瓑rrayList閻ㄥ嫮绮嶉崥锟�  娑擄拷閺夆剝鏆熼幑鐡綼shMap 婢舵碍娼弫鐗堝祦閺勭枆rrayList
			int count = rs.getMetaData().getColumnCount(); //閼惧嘲褰囬崚妤冩畱閺佷即鍣�
			System.out.println(count);
			while(rs.next()){
				HashMap<String ,Object>  row =new  HashMap<String ,Object> ();
				for(int i=0;i<count;i++){
					String key =rs.getMetaData().getColumnLabel(i+1);
					Object  value =rs.getObject(key);
					row.put(key, value);
				}
				list.add(row);
			}
			
			
			
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(pst!=null){
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return  list;
		
		
	}
	
	
	//鏇存柊
	public  static int    update(String sql,Object ... params){
		Connection conn =	getConnection();
		int row=0;
		PreparedStatement pst=null;
		
		try {
			pst  =	conn.prepareStatement(sql);
			if(params!=null){
				for(int i =0;i<params.length;i++){
					pst.setObject(i+1, params[i]);
				}
			}
			row =  pst.executeUpdate();
			System.out.println(sql);
			System.out.println(row);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(pst!=null){
				try {
					pst.close();
					pst=null;
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return  row;
		
		
	}
	
	//閸忔娊妫寸挧鍕爱
	public  static void   close(){
		if(container.get()!=null){
			try {
				container.get().close();
				container.remove();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
	
	
	
	
}
